package com.hui.server.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.hui.server.entity.Employee;
import org.apache.catalina.LifecycleState;
import org.apache.ibatis.annotations.*;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;


/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author hui
 * @since 2022-03-31
 */
public interface EmployeeMapper extends BaseMapper<Employee> {


    /**
     * 分页查询所有员工，并且按照指定条件查询，千万不能忘记加 script标签
     *
     * @param page
     * @param employee
     * @param beginDateScope
     * @return
     */
    @Select("<script>" +
            "select\n" +
            "e.*,\n" +
            "n.id as nid,\n" +
            "n.name as nname,\n" +
            "p.id as pid,\n" +
            "p.name as pname,\n" +
            "d.id as did,\n" +
            "d.name as dname,\n" +
            "j.id as jid,\n" +
            "j.name as jname,\n" +
            "pos.id as posid,\n" +
            "pos.name as posname\n" +
            "from\n" +
            "t_employee e,\n" +
            "t_nation n,\n" +
            "t_politics_status p,\n" +
            "t_department d,\n" +
            "t_joblevel j,\n" +
            "t_position pos\n" +
            "where\n" +

            "e.nationId =n.id\n" +
            "and\n" +
            "e.politicId =p.id\n" +
            "and\n" +
            "e.departmentId =d.id\n" +
            "and\n" +
            "e.jobLevelId =j.id\n" +
            "and\n" +
            "e.posId =pos.id\n" +
            "\n" +

            " <if test=\"employee != null \">\n" +

            " <if test=\"employee.name != null and '' != employee.name \">\n" +
            "AND e.name like concat('%',#{employee.name},'%')\n" +
            "</if>\n" +

            "<if test=\"employee.politicId != null\">\n" +
            "AND e.politicId = #{employee.politicId}\n" +
            "</if>\n" +

            "<if test=\"employee.nationId != null\">\n" +
            "AND e.nationId = #{employee.nationId}\n" +
            "</if>\n" +

            "<if test=\"employee.jobLevelId != null\">\n" +
            "AND e.jobLevelId = #{employee.jobLevelId}\n" +
            "</if>\n" +

            "<if test=\"employee.posId != null\" >\n" +
            "AND e.posId = #{employee.posId}\n" +
            "</if>\n" +

            "<if test=\"employee.departmentId != null\">\n" +
            "AND e.departmentId = #{employee.departmentId}\n" +
            "</if>\n" +

            "<if test=\"null != employee.engageForm and '' != employee.engageForm\">\n" +
            "AND e.engageForm = #{employee.engageForm}\n" +
            "</if>\n" +

            "</if> " +

            "<if test=\"null != beginDateScope and 2 == beginDateScope.length\">" +
            "AND e.beginDate between #{beginDateScope[0]} and #{beginDateScope[1]}\n" +
            "</if>" +

            "ORDER BY\n" +
            "e.id" +
            "</script>")
    @Results(
            value = {
                    @Result(id = true, property = "id", column = "id"),
                    //民族表
                    @Result(property = "nation.id", column = "nid"),
                    @Result(property = "nation.name", column = "nname"),
                    //政治面貌
                    @Result(property = "politicsStatus.id", column = "pid"),
                    @Result(property = "politicsStatus.name", column = "pname"),
                    //部门表
                    @Result(property = "department.id", column = "did"),
                    @Result(property = "department.name", column = "dname"),
                    //职称表
                    @Result(property = "joblevel.id", column = "jid"),
                    @Result(property = "joblevel.name", column = "jname"),
                    //职位表
                    @Result(property = "position.id", column = "posid"),
                    @Result(property = "position.name", column = "posname")
            }
    )
    IPage<Employee> getEmployeeByPage(@Param("page") Page<Employee> page, @Param("employee") Employee employee, @Param("beginDateScope") LocalDate[] beginDateScope);


    /**
     * 传入id如果存在，则按id查员工，否则查所有；需要对应填充员工employee表的其他表字段（主要是其他的表字段太多）
     *
     * @param id
     * @return
     */
    @Select("<script>" +
            "select\n" +
            "e.*,\n" +
            "n.id as nid,\n" +
            "n.name as nname,\n" +
            "p.id as pid,\n" +
            "p.name as pname,\n" +
            "d.id as did,\n" +
            "d.name as dname,\n" +
            "j.id as jid,\n" +
            "j.name as jname,\n" +
            "pos.id as posid,\n" +
            "pos.name as posname\n" +

            "from\n" +

            "t_employee e,\n" +
            "t_nation n,\n" +
            "t_politics_status p,\n" +
            "t_department d,\n" +
            "t_joblevel j,\n" +
            "t_position pos\n" +

            "where\n" +

            "# 通用条件\n" +
            "e.nationId =n.id\n" +
            "and\n" +
            "e.politicId =p.id\n" +
            "and\n" +
            "e.departmentId =d.id\n" +
            "and\n" +
            "e.jobLevelId =j.id\n" +
            "and\n" +
            "e.posId =pos.id\n" +

            "<if test=\"null != id \">\n" +
            "and e.id =#{id} " +
            "</if>" +

            "ORDER BY \n" +
            "e.id " +
            "</script>")
    @Results(
            value = {
                    @Result(id = true, property = "id", column = "id"),
                    //民族表
                    @Result(property = "nation.id", column = "nid"),
                    @Result(property = "nation.name", column = "nname"),
                    //政治面貌
                    @Result(property = "politicsStatus.id", column = "pid"),
                    @Result(property = "politicsStatus.name", column = "pname"),
                    //部门表
                    @Result(property = "department.id", column = "did"),
                    @Result(property = "department.name", column = "dname"),
                    //职称表
                    @Result(property = "joblevel.id", column = "jid"),
                    @Result(property = "joblevel.name", column = "jname"),
                    //职位表
                    @Result(property = "position.id", column = "posid"),
                    @Result(property = "position.name", column = "posname")
            }
    )
    List<Employee> getAllEmployeeOrOneById(@Param("id") Integer id);


    /**
     * 分页查询员工、员工的工资套账与所属部门
     *
     * @param page
     * @return
     */
    @Select("SELECT\n" +
            "e.*,\n" +
            "td.`name` as dname,\n" +
            "s.id AS sid,\n" +
            "s.`name` AS sname,\n" +
            "s.basicSalary AS sbasicSalary,\n" +
            "s.trafficSalary AS strafficSalary,\n" +
            "s.lunchSalary AS slunchSalary,\n" +
            "s.bonus AS sbonus,\n" +
            "s.allSalary AS sallSalary,\n" +
            "s.pensionPer AS spensionPer,\n" +
            "s.pensionBase AS spensionBase,\n" +
            "s.medicalPer AS smedicalPer,\n" +
            "s.medicalBase AS smedicalBase,\n" +
            "s.accumulationFundPer AS saccumulationFundPer,\n" +
            "s.accumulationFundBase AS saccumulationFundBase\n" +
            "FROM\n" +
            "t_employee as e\n" +
            "LEFT JOIN t_salary as s ON e.salaryId = s.id\n" +
            "LEFT JOIN t_department as td ON e.departmentId = td.id\n" +
            "ORDER BY e.id")
    @Results(
            value = {
                    @Result(id = true, property = "id", column = "id"),

                    //部门表
                    @Result(property = "department.name", column = "dname"),
                    //工资套账表
                    @Result(property = "salary.id", column = "sid"),
                    @Result(property = "salary.name", column = "sname"),
                    @Result(property = "salary.basicSalary", column = "sbasicSalary"),
                    @Result(property = "salary.trafficSalary", column = "strafficSalary"),
                    @Result(property = "salary.lunchSalary", column = "slunchSalary"),
                    @Result(property = "salary.bonus", column = "sbonus"),
                    @Result(property = "salary.allSalary", column = "sallSalary"),
                    @Result(property = "salary.pensionBase", column = "spensionBase"),
                    @Result(property = "salary.pensionPer", column = "spensionPer"),
                    @Result(property = "salary.medicalBase", column = "smedicalBase"),
                    @Result(property = "salary.medicalPer", column = "smedicalPer"),
                    @Result(property = "salary.accumulationFundBase", column = "saccumulationFundBase"),
                    @Result(property = "salary.accumulationFundPer", column = "saccumulationFundPer"),

            }
    )
    IPage<Employee> getEmployeeAndSalaryAndDepByPage(Page<Employee> page);


}
